package com.apptonelabs.android;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.apptonelabs.android.model.PNRStatus;
import com.apptonelabs.android.model.PNRType;

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "pnrdb";

	// pnr table name
	private static final String TABLE_PNR = "pnrtb";
	
	// Contacts Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_PNR = "pnr";
	private static final String KEY_DOJ = "doj";
	private static final String KEY_PNR_OBJ = "pnr_obj";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_PNR_TABLE = "CREATE TABLE " + TABLE_PNR + "("
				+ KEY_ID + " INTEGER PRIMARY KEY autoincrement," 
				+ KEY_PNR + " TEXT,"
				+ KEY_DOJ + " BLOB,"
				+ KEY_PNR_OBJ + " TEXT"
				+ ")";
		db.execSQL(CREATE_PNR_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		// db.execSQL("DROP TABLE IF EXISTS " + TABLE_PNR);

		// Create tables again
		// onCreate(db);
	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 * @throws Throwable 
	 */

	// Adding new contact
	void saveOrUpdatePNR(PNRStatus pnr) throws Throwable {
		if(this.getPNR(pnr.getPnr()) == null) {
			SQLiteDatabase db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(KEY_PNR, pnr.getPnr());
			values.put(KEY_DOJ, pnr.getDoj());
			values.put(KEY_PNR_OBJ, PNRStatus.serialize(pnr));

			// Inserting Row
			db.insert(TABLE_PNR, null, values);
			db.close(); // Closing database connection
		} else {
			this.updatePNR(pnr);
		}
	}

	// Getting single contact
	PNRStatus getPNR(String pnr) throws Throwable, ClassNotFoundException {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_PNR, new String[] { KEY_ID, KEY_PNR, KEY_DOJ, KEY_PNR_OBJ},
				KEY_PNR + "=?",
				new String[] { String.valueOf(pnr) }, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		
		PNRStatus val = null;
		try {
			int id = cursor.getInt(0);
			val = PNRStatus.deserialize(cursor.getBlob(3));
			val.setId(id);
		} catch (Exception e) {
			return null;
		} finally {
			db.close();
		}
		return val;
	}

	// Getting All Contacts
	public List<PNRStatus> getAllContacts() throws Throwable, ClassNotFoundException {
		List<PNRStatus> list = new ArrayList<PNRStatus>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_PNR;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				PNRStatus contact = PNRStatus.deserialize(cursor.getString(2).getBytes());
				list.add(contact);
			} while (cursor.moveToNext());
		}
		db.close();
		// return contact list
		return list;
	}

	// Updating single contact
	public int updatePNR(PNRStatus pnr) throws Throwable {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_PNR_OBJ, PNRStatus.serialize(pnr));
		values.put(KEY_PNR, pnr.getPnr());

		int val = db.update(TABLE_PNR, values, KEY_PNR + " = ?",
				new String[] { String.valueOf(pnr.getPnr()) }); 
		db.close();
		// updating row
		return val;
	}

	// Deleting single contact
	public void deleteContact(String pnr) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_PNR, KEY_PNR + " = ?",
				new String[] { String.valueOf(pnr) });
		db.close();
	}

	// Getting contacts Count
	public int getPNRCount() {
		String countQuery = "SELECT  * FROM " + TABLE_PNR;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);

		int count = cursor.getCount();
		
		cursor.close();
		db.close();
		// return count
		return count;
	}

	// Getting contacts Count
	public int getFuturePNRCount() {
		Date date = new Date();
		date.setHours(0);
		date.setMinutes(0);
		date.setSeconds(0);
		long ts = date.getTime();
		ts = (ts/1000) * 1000;
		ts += TimeZone.getDefault().getOffset(ts);
		
		String countQuery = "SELECT  * FROM " + TABLE_PNR + " where " + KEY_DOJ + " > " + ts;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);

		int count = cursor.getCount();

		cursor.close();
		db.close();
		// return count
		return count;
	}

	// Getting contacts Count
	public int getTodayPNRCount() {
		Date date = new Date();
		date.setHours(0);
		date.setMinutes(0);
		date.setSeconds(0);
		long ts = date.getTime();
		ts = (ts/1000) * 1000;
		ts += TimeZone.getDefault().getOffset(ts);
		
		String countQuery = "SELECT  * FROM " + TABLE_PNR + " where " + KEY_DOJ + " = " + ts;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);

		int count = cursor.getCount();

		cursor.close();
		db.close();
		// return count
		return count;
	}

	// Getting contacts Count
	public int getPastPNRCount() {
		Date date = new Date();
		date.setHours(0);
		date.setMinutes(0);
		date.setSeconds(0);
		long ts = date.getTime();
		ts = (ts/1000) * 1000;
		ts += TimeZone.getDefault().getOffset(ts);
		
		String countQuery = "SELECT  * FROM " + TABLE_PNR + " where " + KEY_DOJ + " < " + ts;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);

		int count = cursor.getCount();

		cursor.close();
		db.close();
		// return count
		return count;
	}

	// Getting contacts Count
	public List<PNRStatus> getPNRList(PNRType type) {
		List<PNRStatus> list = new ArrayList<PNRStatus>();
		Date date = new Date();
		date.setHours(0);
		date.setMinutes(0);
		date.setSeconds(0);
		long ts = date.getTime();
		ts = (ts/1000) * 1000;
		ts += TimeZone.getDefault().getOffset(ts);
		
		String op = "=";
		if(type == PNRType.FUTURE) {
			op = ">";
		} else if(type == PNRType.TODAY) {
			op = "=";
		} else if(type == PNRType.PAST) {
			op = "<";
		} else {
			op = "=";
		}
		
		String countQuery = "SELECT  * FROM " + TABLE_PNR + " where " + KEY_DOJ + " " + op + " " + ts;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);

		if (cursor != null) {
		    for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		    {
				PNRStatus val = null;
				try {
					int id = cursor.getInt(0);
					val = PNRStatus.deserialize(cursor.getBlob(3));
					val.setId(id);
					
					list.add(val);
				} catch (Exception e) {
					Log.d("PNR", "DB Error=" + e.getMessage(), e);
				}

		    }
		}
		
		db.close();
		
		return list;
	}

	public void eraseAllData() {
		String query = "DELETE  FROM " + TABLE_PNR;
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL(query);
	}

}